/*
 * Copyright (c) 2025 Industrial Software Feature Database
 */
package com.comac.ins.isfd.util;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import java.util.function.BiConsumer;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.BeanUtils;

import cn.hutool.core.collection.CollectionUtil;
import com.comac.ins.common.core.utils.StringUtils;
import com.comac.ins.isfd.domain.bo.FastenerInfoBo;
import com.comac.ins.isfd.domain.bo.FastenerModelBo;
import com.comac.ins.isfd.domain.bo.query.ResultSplitAndSaveExcelTablesBo;
import lombok.extern.slf4j.Slf4j;

@Slf4j
public class FastenerSplitExcelUtils {

    private static final DataFormatter dataFormatter = new DataFormatter();

    public static List<ResultSplitAndSaveExcelTablesBo> splitAndSaveExcelTables(InputStream inputStream, String originalFileName, String outputDir) throws IOException {
        List<ResultSplitAndSaveExcelTablesBo> resultSplitAndSaveExcelTablesBoList = new ArrayList<>();
        File dir = new File(outputDir);
        if (!dir.exists()) dir.mkdirs();

        // 根据文件后缀判断格式
        String fileExt = originalFileName.substring(originalFileName.lastIndexOf(".") + 1).toLowerCase();
        if (!"xls".equalsIgnoreCase(fileExt) && !"xlsx".equalsIgnoreCase(fileExt)) {
            throw new IllegalArgumentException("不支持的文件格式，仅支持xls和xlsx");
        }

        try (Workbook workbook = WorkbookFactory.create(inputStream)) {
            String baseName = originalFileName.replaceAll("\\.xlsx?$", "");

            for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
                Sheet sheet = workbook.getSheetAt(sheetIndex);
                List<List<String>> currentTable = new ArrayList<>();
                List<List<Cell>> originalCells = new ArrayList<>();
                int tableIndex = 1;

                for (Row row : sheet) {
                    if (isRowEmpty(row)) {
                        if (!currentTable.isEmpty()) {
                            ResultSplitAndSaveExcelTablesBo resultSplitAndSaveExcelTablesBo = new ResultSplitAndSaveExcelTablesBo();
                            FastenerModelBo fastenerModelBo = conversionJson(currentTable);
                            resultSplitAndSaveExcelTablesBo.setFastenerModelBo(fastenerModelBo);
                            File saved = saveTableToFile(baseName, sheetIndex, tableIndex++, currentTable, outputDir, originalCells, fileExt);
                            resultSplitAndSaveExcelTablesBo.setResultExcel(saved.getAbsolutePath());
                            resultSplitAndSaveExcelTablesBoList.add(resultSplitAndSaveExcelTablesBo);
                            currentTable = new ArrayList<>();
                            originalCells = new ArrayList<>();
                        }
                        continue;
                    }

                    List<String> rowData = new ArrayList<>();
                    List<Cell> origRowCells = new ArrayList<>();
                    for (Cell cell : row) {
                        String cellValue = getCellValueAsString(cell);
                        rowData.add(cellValue.trim());
                        origRowCells.add(cell);
                    }
                    currentTable.add(rowData);
                    originalCells.add(origRowCells);
                }

                if (!currentTable.isEmpty()) {
                    ResultSplitAndSaveExcelTablesBo resultSplitAndSaveExcelTablesBo = new ResultSplitAndSaveExcelTablesBo();
                    FastenerModelBo fastenerModelBo = conversionJson(currentTable);
                    resultSplitAndSaveExcelTablesBo.setFastenerModelBo(fastenerModelBo);

                    File saved = saveTableToFile(baseName, sheetIndex, tableIndex++, currentTable, outputDir, originalCells, fileExt);
                    resultSplitAndSaveExcelTablesBo.setResultExcel(saved.getAbsolutePath());
                    resultSplitAndSaveExcelTablesBoList.add(resultSplitAndSaveExcelTablesBo);
                }
            }
        }

        return resultSplitAndSaveExcelTablesBoList;
    }

    private static FastenerModelBo conversionJson(List<List<String>> currentTable) {
        //第一行
        List<String> fastenerModelList = currentTable.get(0);
        if (CollectionUtil.isNotEmpty(fastenerModelList)) {

            String fastenerModel = fastenerModelList.get(0);
            String description = fastenerModel.replaceAll(".*】", "").trim();
            fastenerModel = fastenerModel.replace("【", "").replaceAll("】.*", "");
            String[] components = fastenerModel.split(",");
            FastenerModelBo fastenerModelBo = new FastenerModelBo();

            // 根据数组长度判断赋值方式
            if (components.length == 8) {
                // 长度为7时的赋值方式
                fastenerModelBo.setFastenerGrade(components[0].trim());
                fastenerModelBo.setPlateName(components[1].trim());
                fastenerModelBo.setFastenerStandards(components[2].trim());
                fastenerModelBo.setMargin(components[3].trim());
                fastenerModelBo.setFractureStrength(components[4].trim());
                fastenerModelBo.setShearConnection(components[5].trim());
                fastenerModelBo.setAllowableType(components[6].trim());
            } else if (components.length == 9) {
                // 长度为8时的赋值方式（合并第2和第3个元素）

                //与孔配合
                if (components[1].trim().startsWith("与孔")) {
                    //普通
                    fastenerModelBo.setFastenerGrade(components[0].trim());
                    fastenerModelBo.setPlateName(components[1].trim() + "," + components[2].trim());
                    fastenerModelBo.setFastenerStandards(components[3].trim());
                    fastenerModelBo.setMargin(components[4].trim());
                    fastenerModelBo.setFractureStrength(components[5].trim());
                    fastenerModelBo.setShearConnection(components[6].trim());
                    fastenerModelBo.setAllowableType(components[7].trim());
                } else {
                    //普通
                    fastenerModelBo.setFastenerGrade(components[0].trim());
                    fastenerModelBo.setPlateName(components[1].trim());
                    fastenerModelBo.setFastenerStandards(components[2].trim() + "," + components[3].trim());
                    fastenerModelBo.setMargin(components[4].trim());
                    fastenerModelBo.setFractureStrength(components[5].trim());
                    fastenerModelBo.setShearConnection(components[6].trim());
                    fastenerModelBo.setAllowableType(components[7].trim());
                }
            }
            if (description.contains("孔挤压强度")) {
                fastenerModelBo.setDataType("孔挤压强度");
            } else {
                fastenerModelBo.setDataType("连接许用值(极限)");
            }

            // 第二行 判断按照毫米还是英寸
            List<String> unitStringList = currentTable.get(1);
            String unitString = unitStringList.get(2);
            String unit = judgeThicknessType(unitString);
            fastenerModelBo.setUnit(unit);

            // 第三行到第七行 C列开始是二维数据的公共部分
            List<FastenerInfoBo> templateInfoTitle = new LinkedList<>();
            List<String> row = currentTable.get(2);
            for (int i = 2; i < row.size(); i++) {
                if (StringUtils.isNotBlank(row.get(i))) {
                    templateInfoTitle.add(new FastenerInfoBo());
                }
            }
            // 第三行 修正后的紧固件单剪强度(磅）
            fasteneraInfoBoFiller(row, templateInfoTitle, FastenerInfoBo::setShearStrength);
            // 第四行 紧固件失效的最薄板厚度（英寸）
            fasteneraInfoBoFiller(currentTable.get(3), templateInfoTitle, FastenerInfoBo::setThickness2shear);
            List<FastenerInfoBo> fastenerInfoList = new LinkedList<>();
            if ("imperial".equals(unit)) {
                // 第五行 名义直径
                fasteneraInfoBoFiller(currentTable.get(4), templateInfoTitle, FastenerInfoBo::setNominalDiameter);
                // 第六行 紧固件头部高度
                fasteneraInfoBoFiller(currentTable.get(5), templateInfoTitle, FastenerInfoBo::setHeadHeight);
                // 第七行 直径代码
                fasteneraInfoBoFiller(currentTable.get(6), templateInfoTitle, FastenerInfoBo::setDiameterCode);

                // 第八行开始 具体的部件
                for (int i = 7; i < currentTable.size(); i++) {
                    row = currentTable.get(i);
                    // A列 板厚（毫米）
                    String thicknessMM = row.get(0);
                    // B列 板厚（英寸）
                    String thicknessInches = row.get(1);
                    for (int j = 2; j < row.size() && j < templateInfoTitle.size() + 2; j++) {
                        FastenerInfoBo templateInfoBo = templateInfoTitle.get(j - 2);
                        FastenerInfoBo fastenerInfoBo = new FastenerInfoBo();
                        BeanUtils.copyProperties(templateInfoBo, fastenerInfoBo);
                        fastenerInfoBo.setThicknessMM(thicknessMM);
                        fastenerInfoBo.setThicknessInches(thicknessInches);
                        fastenerInfoBo.setAllowableValue(row.get(j));
                        fastenerInfoList.add(fastenerInfoBo);
                    }
                }
            } else {
                // 第五行 名义直径
                fasteneraInfoBoFiller(currentTable.get(5), templateInfoTitle, FastenerInfoBo::setNominalDiameter);
                // 第六行 紧固件头部高度
                fasteneraInfoBoFiller(currentTable.get(6), templateInfoTitle, FastenerInfoBo::setHeadHeight);
                // 第七行 直径代码
                fasteneraInfoBoFiller(currentTable.get(7), templateInfoTitle, FastenerInfoBo::setDiameterCode);

                // 第八行开始 具体的部件
                for (int i = 8; i < currentTable.size(); i++) {
                    row = currentTable.get(i);
                    // A列 板厚（毫米）
                    String thicknessMM = row.get(1);
                    // B列 板厚（英寸）
                    String thicknessInches = row.get(0);
                    for (int j = 2; j < row.size() && j < templateInfoTitle.size() + 2; j++) {
                        FastenerInfoBo templateInfoBo = templateInfoTitle.get(j - 2);
                        FastenerInfoBo fastenerInfoBo = new FastenerInfoBo();
                        BeanUtils.copyProperties(templateInfoBo, fastenerInfoBo);
                        fastenerInfoBo.setThicknessMM(thicknessMM);
                        fastenerInfoBo.setThicknessInches(thicknessInches);
                        fastenerInfoBo.setAllowableValue(row.get(j));
                        fastenerInfoList.add(fastenerInfoBo);
                    }
                }
            }

            fastenerModelBo.setFastenerInfoList(fastenerInfoList);
            return fastenerModelBo;
        }
        return new FastenerModelBo();
    }

    public static String judgeThicknessType(String typeString) {
        if (!StringUtils.isNotBlank(typeString)) {
            return "";
        }

        if (typeString.contains("牛顿")) {
            return "metric";
        } else if (typeString.contains("磅")) {
            return "imperial";
        } else {
            return "";
        }
    }

    private static void fasteneraInfoBoFiller(List<String> row, List<FastenerInfoBo> fastenerInfoBoList, BiConsumer<FastenerInfoBo, String> setter) {
        for (int i = 2; i < row.size(); i++) {
            String infoLine = row.get(i);
            if (StringUtils.isNotBlank(infoLine)) {
                setter.accept(fastenerInfoBoList.get(i - 2), infoLine);
            }
        }
    }

    private static File saveTableToFile(String baseName, int sheetIndex, int tableIndex,
                                        List<List<String>> tableData, String outputDir,
                                        List<List<Cell>> originalCells, String fileExt) throws IOException {
        Workbook newWorkbook = "xls".equalsIgnoreCase(fileExt) ? new HSSFWorkbook() : new XSSFWorkbook();
        Sheet newSheet = newWorkbook.createSheet("Sheet1");

        for (int rowIdx = 0; rowIdx < tableData.size(); rowIdx++) {
            Row newRow = newSheet.createRow(rowIdx);
            List<String> rowData = tableData.get(rowIdx);
            List<Cell> origRowCells = originalCells.get(rowIdx);

            for (int colIdx = 0; colIdx < rowData.size(); colIdx++) {
                Cell newCell = newRow.createCell(colIdx);
                Cell origCell = origRowCells.get(colIdx);

                if (origCell != null) {
                    // 拷贝值
                    copyCellValue(origCell, newCell);
                    // 拷贝样式
                    if (origCell.getCellStyle() != null) {
                        CellStyle newStyle = newWorkbook.createCellStyle();
                        newStyle.cloneStyleFrom(origCell.getCellStyle());
                        newCell.setCellStyle(newStyle);
                    }
                } else {
                    newCell.setCellValue(rowData.get(colIdx));
                }
            }
        }

        String filename = String.format("%s_Sheet%d_Table%d.xlsx", baseName, sheetIndex + 1, tableIndex);
        File file = new File(outputDir, filename);
        try (FileOutputStream out = new FileOutputStream(file)) {
            newWorkbook.write(out);
        }
        newWorkbook.close();
        return file;
    }

    private static void copyCellValue(Cell src, Cell dest) {
        switch (src.getCellType()) {
            case STRING:
                dest.setCellValue(src.getStringCellValue());
                break;
            case NUMERIC:
                dest.setCellValue(src.getNumericCellValue());
                break;
            case BOOLEAN:
                dest.setCellValue(src.getBooleanCellValue());
                break;
            case FORMULA:
                dest.setCellFormula(src.getCellFormula());
                break;
            case BLANK:
                dest.setBlank();
                break;
            default:
                dest.setCellValue(src.toString());
        }
    }

    private static boolean isRowEmpty(Row row) {
        if (row == null) return true;
        for (Cell cell : row) {
            if (cell != null && cell.getCellType() != CellType.BLANK) {
                String cellValue = getCellValueAsString(cell);
                if (cellValue != null && !cellValue.trim().isEmpty()) {
                    return false;
                }
            }
        }
        return true;
    }

    private static String getCellValueAsString(Cell cell) {
        if (cell == null) return "";
        return dataFormatter.formatCellValue(cell);
    }
}
